﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Data.SqlClient
Imports System.Security.Cryptography
Imports System.Text
Imports log4net
Public Class Connection
    'Public Shared CONNECTION_STRING As String = ConfigurationSettings.AppSettings("MyDbConn")
    Private Shared ReadOnly log As ILog = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)
    Private Shared con As SqlConnection
    Public Sub New()
    End Sub
    Public Shared Sub Close()
        ''con.Close()
        GC.Collect()
    End Sub

    Public Shared ReadOnly Property ConnectionString() As String
        Get
            Return ConfigurationManager.ConnectionStrings("ks24ConnectionString").ConnectionString
        End Get
    End Property
    Public Shared Function GetConnection() As SqlConnection
        Dim sConnect As String = ConfigurationManager.ConnectionStrings("ks24ConnectionString").ConnectionString
        Try
            If con Is Nothing Then
                con = New SqlConnection(sConnect)
            End If
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
        Catch e As SqlException
            log.Error(e)
            log.Error("ConnectString: " & sConnect)
        End Try
        Return con
    End Function
    Public Shared Function ExcuteQuery(ByVal query As String) As Integer
        Dim backValue As Integer = -1
        Try
            Dim cmd As New SqlCommand(query, GetConnection())
            backValue = cmd.ExecuteNonQuery()

        Catch e As SqlException
            con = Nothing
            log.Error("SQL: " + query)
            Throw e
        End Try
        Return backValue
    End Function

    Public Shared Function ExcuteScalar(ByVal query As String, Optional ByVal isNewConnection As Boolean = False) As Object
        Dim result As Object = Nothing
        Try
            If isNewConnection Then
                Dim sConnect As String = ConfigurationManager.ConnectionStrings("ks24ConnectionString").ConnectionString
                con = New SqlConnection(sConnect)
                con.Open()
            Else
                con = GetConnection()
            End If
            Dim cmd As New SqlCommand(query, con)
            If Not IsNothing(cmd.ExecuteScalar) Then
                result = cmd.ExecuteScalar()

                If isNewConnection Then
                    con.Close()
                End If
            End If
        Catch ex As Exception
            con.Close()
            con = Nothing
            log.Error(ex)
        End Try
        Return result
    End Function

    Public Shared Function ExcuteReader(ByVal query As String) As SqlDataReader
        Dim reader As SqlDataReader = Nothing
        Try
            Dim connect As New SqlConnection(ConnectionString)
            Dim cmd As New SqlCommand(query, connect)
            connect.Open()
            reader = cmd.ExecuteReader()
        Catch ex As SqlException
            con.Close()
            con = Nothing
            log.Error(ex)
            log.Error("SQL ExcuteReader: " + query)
        End Try
        Return reader
    End Function

    Public Shared Function ExcuteDataSet(ByVal query As String, Optional table As String = "") As DataSet
        Dim dataset As New DataSet()
        Try
            Dim dataAdapter As New SqlDataAdapter(query, GetConnection())
            If dataAdapter IsNot Nothing Then
                If table.Equals("") Then
                    dataAdapter.Fill(dataset)
                Else
                    dataAdapter.Fill(dataset, table)
                End If

            End If
        Catch e As SqlException
            con.Close()
            con = Nothing
            log.Error(e)
            log.Error("SQL ExcuteReader: " + query)
        End Try
        Return dataset
    End Function

    Public Shared Sub CheckCloseConnect()
        Try
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        Catch ex As Exception
            ''Throw ex
        End Try
    End Sub

    Public Shared Sub Dispose()
        Try
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
            End If
            con = Nothing
        Catch ex As Exception
            con = Nothing
            log.Error(ex)
        End Try
    End Sub

    Public Shared Function GetDataByLiveryCoachCer(ByVal encryptedccData As Byte()) As String
        Dim value As String = ""
        Try
            Dim sql As String = ""
            sql = String.Format("{0} open symmetric key CCENDE_KEy_01 decryption by certificate LiveryCoachCer; ", sql)
            sql = String.Format("{0} select convert(varchar(50), decryptbykey(@encryptValue)); ", sql, encryptedccData)
            sql = String.Format("{0} ", sql)

            Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ks24ConnectionString").ConnectionString)
                Dim myCommand As New SqlCommand(sql, myConnection)
                myCommand.Parameters.Add("@encryptValue", SqlDbType.VarBinary).Value = encryptedccData
                myConnection.Open()
                value = myCommand.ExecuteScalar()
                myCommand.Dispose()
                myConnection.Close()
            End Using

        Catch ex As Exception
            log.Error(ex)
        End Try
        Return value
    End Function

    Public Shared Function ExecStoreProcGetReturnvalue(ByVal procName As String, ByVal outputParam As String, ByVal outputDataType As SqlDbType, Optional ByVal lstInputParam As List(Of KeyValuePair(Of String, Object)) = Nothing) As Object
        Dim result As Object = Nothing
        Try
            Using conn As SqlConnection = New SqlConnection(ConnectionString)
                Dim cmd As New SqlCommand()
                cmd.CommandText = procName
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Connection = conn

                If lstInputParam IsNot Nothing Then
                    For i As Integer = 0 To lstInputParam.Count - 1
                        Dim inParam As New SqlParameter(lstInputParam(i).Key, lstInputParam(i).Value)
                        inParam.Direction = ParameterDirection.Input
                        cmd.Parameters.Add(inParam)
                    Next
                End If

                Dim returnParam As SqlParameter = New SqlParameter(outputParam, outputDataType)
                returnParam.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(returnParam)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                result = cmd.Parameters(outputParam).Value
                cmd.Dispose()

            End Using

        Catch ex As Exception
            log.Error(ex)
        End Try
        Return result
    End Function

End Class
